"""Create ACME tables

Revision ID: 024a0fe4cc9e
Revises:
Create Date: 2025-06-09 23:50:55.552746

"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
import sqlmodel


# revision identifiers, used by Alembic.
revision: str = "024a0fe4cc9e"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "acme_accounts",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column(
            "key_id", sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False
        ),
        sa.Column("public_key", sa.Text(), nullable=True),
        sa.Column(
            "status",
            sa.Enum("VALID", "DEACTIVATED", "REVOKED", name="accountstatus"),
            nullable=False,
        ),
        sa.Column("contact", sa.JSON(), nullable=True),
        sa.Column("terms_of_service_agreed", sa.Boolean(), nullable=False),
        sa.Column("external_account_binding", sa.JSON(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_acme_accounts_key_id"), "acme_accounts", ["key_id"], unique=True
    )
    op.create_table(
        "acme_nonces",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column(
            "nonce", sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False
        ),
        sa.Column("used", sa.Boolean(), nullable=False),
        sa.Column("expires", sa.DateTime(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_acme_nonces_nonce"), "acme_nonces", ["nonce"], unique=True)
    op.create_table(
        "acme_orders",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column(
            "order_id", sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False
        ),
        sa.Column("account_id", sa.Integer(), nullable=False),
        sa.Column(
            "status",
            sa.Enum(
                "PENDING", "READY", "PROCESSING", "VALID", "INVALID", name="orderstatus"
            ),
            nullable=False,
        ),
        sa.Column("identifiers", sa.JSON(), nullable=True),
        sa.Column("not_before", sa.DateTime(), nullable=True),
        sa.Column("not_after", sa.DateTime(), nullable=True),
        sa.Column("error", sa.JSON(), nullable=True),
        sa.Column("authorizations", sa.JSON(), nullable=True),
        sa.Column(
            "finalize", sqlmodel.sql.sqltypes.AutoString(length=512), nullable=True
        ),
        sa.Column(
            "certificate", sqlmodel.sql.sqltypes.AutoString(length=512), nullable=True
        ),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=True),
        sa.Column("expires", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(
            ["account_id"],
            ["acme_accounts.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_acme_orders_order_id"), "acme_orders", ["order_id"], unique=True
    )
    op.create_table(
        "acme_authorizations",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column(
            "authz_id", sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False
        ),
        sa.Column("order_id", sa.Integer(), nullable=False),
        sa.Column("identifier", sa.JSON(), nullable=True),
        sa.Column(
            "status",
            sa.Enum(
                "PENDING",
                "VALID",
                "INVALID",
                "DEACTIVATED",
                "EXPIRED",
                "REVOKED",
                name="authorizationstatus",
            ),
            nullable=False,
        ),
        sa.Column("expires", sa.DateTime(), nullable=False),
        sa.Column("wildcard", sa.Boolean(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(
            ["order_id"],
            ["acme_orders.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_acme_authorizations_authz_id"),
        "acme_authorizations",
        ["authz_id"],
        unique=True,
    )
    op.create_table(
        "acme_certificates",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column(
            "cert_id", sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False
        ),
        sa.Column("order_id", sa.Integer(), nullable=False),
        sa.Column(
            "serial_number",
            sqlmodel.sql.sqltypes.AutoString(length=255),
            nullable=False,
        ),
        sa.Column("certificate_pem", sa.Text(), nullable=True),
        sa.Column(
            "status",
            sa.Enum("VALID", "EXPIRED", "REVOKED", name="certificatestatus"),
            nullable=False,
        ),
        sa.Column("subject", sa.JSON(), nullable=True),
        sa.Column("not_before", sa.DateTime(), nullable=False),
        sa.Column("not_after", sa.DateTime(), nullable=False),
        sa.Column("revoked_at", sa.DateTime(), nullable=True),
        sa.Column("revocation_reason", sa.Integer(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(
            ["order_id"],
            ["acme_orders.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("serial_number"),
    )
    op.create_index(
        op.f("ix_acme_certificates_cert_id"),
        "acme_certificates",
        ["cert_id"],
        unique=True,
    )
    op.create_table(
        "acme_challenges",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column(
            "challenge_id", sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False
        ),
        sa.Column("authorization_id", sa.Integer(), nullable=False),
        sa.Column("type", sa.Enum("HTTP_01", name="challengetype"), nullable=False),
        sa.Column(
            "status",
            sa.Enum(
                "PENDING", "PROCESSING", "VALID", "INVALID", name="challengestatus"
            ),
            nullable=False,
        ),
        sa.Column(
            "token", sqlmodel.sql.sqltypes.AutoString(length=255), nullable=False
        ),
        sa.Column("url", sqlmodel.sql.sqltypes.AutoString(length=512), nullable=True),
        sa.Column("validated", sa.DateTime(), nullable=True),
        sa.Column("error", sa.JSON(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(
            ["authorization_id"],
            ["acme_authorizations.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_acme_challenges_challenge_id"),
        "acme_challenges",
        ["challenge_id"],
        unique=True,
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f("ix_acme_challenges_challenge_id"), table_name="acme_challenges")
    op.drop_table("acme_challenges")
    op.drop_index(op.f("ix_acme_certificates_cert_id"), table_name="acme_certificates")
    op.drop_table("acme_certificates")
    op.drop_index(
        op.f("ix_acme_authorizations_authz_id"), table_name="acme_authorizations"
    )
    op.drop_table("acme_authorizations")
    op.drop_index(op.f("ix_acme_orders_order_id"), table_name="acme_orders")
    op.drop_table("acme_orders")
    op.drop_index(op.f("ix_acme_nonces_nonce"), table_name="acme_nonces")
    op.drop_table("acme_nonces")
    op.drop_index(op.f("ix_acme_accounts_key_id"), table_name="acme_accounts")
    op.drop_table("acme_accounts")
    # ### end Alembic commands ###
